package org.xobo.finance.helper.invoice.repository;

import java.time.LocalDate;
import java.util.Collection;
import java.util.Map;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.xobo.finance.helper.invoice.entity.Invoice;


@Repository
public interface InvoiceRepository extends CrudRepository<Invoice, Long> {

  @Query(
      value = "select i.company_id companyId, sum(i.price_plus_taxes) totalAmount from invoice i where i.deleted = 0 and i.invoice_status = 1 and i.invoice_date_issued >= :startDate and i.company_id in :companyIdList group by i.company_id",
      nativeQuery = true)
  Collection<Map<String, Object>> loadCompanyAmount(@Param("startDate") LocalDate startDate,
      @Param("companyIdList") Collection<Long> companyIdList);
  
  
  @Query(value = "SELECT\n" + 
      "    i.company_id companyId,\n" + 
      "    date_format(i.invoice_date_issued,'%Y-%m') yearMonth,\n" + 
      "    sum( i.price_plus_taxes ) totalAmount \n" + 
      "FROM\n" + 
      "    invoice i \n" + 
      "WHERE\n" + 
      "    i.deleted = 0 \n" + 
      "    and i.invoice_date_issued >= :startDate\n" + 
      "    and i.invoice_status = 1\n" +
      "    and i.company_id = :companyId\n" + 
      "GROUP BY\n" + 
      "     date_format(i.invoice_date_issued,'%Y-%m')\n" + 
      "\n" + 
      "ORDER BY date_format(i.invoice_date_issued,'%Y-%m') desc", nativeQuery = true)
  Collection<Map<String, Object>> loadCompanyYearMonthAmount(@Param("startDate") LocalDate startDate,
      @Param("companyId") Long companyId);

  @Query("select i.id from Invoice i where i.deleted = 0 and i.invoiceCode = :invoiceCode and i.invoiceNo = :invoiceNo")
  Long findInvoiceId(String invoiceCode, String invoiceNo);
}
